﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Reflection;

namespace DAL
{
    public class Students
    {
        DbHelper dbHelper = null;
        public Students()
        {
            dbHelper = new DbHelper();
        
        }

        public bool ModifyStudent(MODEL.Students model)
        {
            StringBuilder sbSql =new StringBuilder("update Students set ");
            Type modeType = model.GetType();
            PropertyInfo[] pros = modeType.GetProperties();
            List<SqlParameter> paras = new List<SqlParameter>();
           // int index = 0;
            foreach (PropertyInfo pi in pros)
            {
                if (!pi.Name.Equals("SID") && !pi.Name.Contains("Addtime"))
                {
                    if (pi.GetValue(model, null) != null && !pi.GetValue(model, null).ToString().Equals(""))
                    {
                        sbSql.Append(pi.Name + "=@" + pi.Name + ",");
                        paras.Add(new SqlParameter("@" + pi.Name, pi.GetValue(model, null)));
                        
                    }                        
                }               
            }
            string strsql = sbSql.ToString().Trim(',');
            strsql += " where SID=@SID";
            paras.Add(new SqlParameter("@SID", model.SID));
            return dbHelper.ExecuteCommand(strsql, paras.ToArray())>0;
        }

        #region 根据查询条件返回单个学生实体对象
        /// <summary>
        /// 根据查询条件返回单个学生实体对象
        /// </summary>
        /// <param name="paras"></param>
        /// <returns></returns>
        public MODEL.Students QuerySingleByCondition(SqlParameter[] paras)
        {
            MODEL.Students model = null;
            StringBuilder sbSql = new StringBuilder("select * from Students s,Classes c where s.SCId=c.CId  and SIsDel=0 ");
            if (paras != null)
            {
                for (int i = 0; i < paras.Length; i++)//循环所有参数
                {
                    SqlParameter p = paras[i];
                    sbSql.Append(" and ");//第二个参数开始 在前面加 and
                    sbSql.Append(p.ParameterName.Substring(1));
                    sbSql.Append("=" + p.ParameterName);
                }
            }
            //读取数据库 返回查询到的数据表
            DataRow dr = dbHelper.GetDataRow(sbSql.ToString(), paras);
            //准备要返回的泛型集合
            if (dr != null)//如果查询到的行数大于0
            {
                model = new MODEL.Students();
                SetDr2Model(dr, model);
            }
            return model;

        }

        #endregion

        #region 根据ID获得单个学生对象
        /// <summary>
        /// 根据ID获得单个学生对象
        /// </summary>
        /// <param name="strId">ID</param>
        /// <returns></returns>
        public MODEL.Students GetStudentById(string strId)
        {
            SqlParameter[] paras = { 
                                       new SqlParameter("@SID",strId)
                                   };

            return QuerySingleByCondition(paras);
        } 
        #endregion

        #region 返回单个学生实体对象
        /// <summary>
        /// 返回单个学生实体对象
        /// </summary>
        /// <param name="uName"></param>
        /// <param name="uPwd"></param>
        /// <returns></returns>
        public MODEL.Students QuerySingleByCondition(string uName, string uPwd)
        {
            MODEL.Students model = null;
            string sqlStr = "select * from Students s,Classes c where s.SCId = c.CId and SIsDel = 0 and SLoginName = @SLoginName and SPwd = @SPwd";
            SqlParameter[] paras = {
                                   new SqlParameter("@SLoginName",uName),
                                   new SqlParameter("@SPwd",uPwd)
                                   };
            DataRow dr = dbHelper.GetDataRow(sqlStr,paras);
            if (dr !=null)
            {
                model = new MODEL.Students();
                SetDr2Model(dr, model);
            }
            return model;
        } 

        #endregion

       

        public List<MODEL.Students> QueryListByCondition(SqlParameter[] paras)
        {
            MODEL.Students model = null;
            StringBuilder sbSql = new StringBuilder("select * from Students s,Classes c where s.SCId=c.CId  and SIsDel=0 ");
            if (paras != null)
            {
                for (int i = 0; i < paras.Length; i++)//循环所有参数
                {
                    SqlParameter p = paras[i];
                    sbSql.Append(" and ");//第二个参数开始 在前面加 and
                    sbSql.Append(p.ParameterName.Substring(1));
                    sbSql.Append("=" + p.ParameterName);
                }
            }
            //读取数据库 返回查询到的数据表
            DataTable dt = (paras == null) ? dbHelper.GetDataTable(sbSql.ToString()) : dbHelper.GetDataTable(sbSql.ToString(), paras);
            //准备要返回的泛型集合
            List<MODEL.Students> list = null;
            if (dt.Rows.Count > 0)//如果查询到的行数大于0
            {
                list = new List<MODEL.Students>();
                foreach (DataRow dr in dt.Rows)
                {
                    model = new MODEL.Students();
                    SetDr2Model(dr, model);
                    list.Add(model);
                }
            }
            return list;
        }

        public void SetDr2Model(DataRow dr,MODEL.Students model)
        {
            if (dr["SID"].ToString() != "")
                {
                    model.SID = int.Parse(dr["SID"].ToString());
                }
                if (dr["SCID"].ToString() != "")
                {
                    model.SCID = int.Parse(dr["SCID"].ToString());
                    //使用班级数据类 装填 学生对象里的所属班级实体对象
                    model.SClasses = new MODEL.Classes();
                    new DAL.Classes().SetDr2Model(dr,model.SClasses);
                }
                model.SLoginName = dr["SLoginName"].ToString();
                model.SCnName = dr["SCnName"].ToString();
                model.SPwd = dr["SPwd"].ToString();
                if (dr["SGender"].ToString() != "")
                {
                    model.SGender = bool.Parse(dr["SGender"].ToString());
                }
                model.STel = dr["STel"].ToString();
                if (dr["SIsDel"].ToString() != "")
                {
                    model.SIsDel = bool.Parse(dr["SIsDel"].ToString());
                }
                if (dr["SAddtime"].ToString() != "")
                {
                    model.SAddtime = DateTime.Parse(dr["SAddtime"].ToString());
                } 


        }

        /// <summary>
        /// soft del
        /// </summary>
        /// <param name="id">要删除的id</param>
        /// <returns></returns>
        public bool DelSoft(string id)
        {
            string strSql = "update Students set SIsDel = 1 where SID = @id ";
            SqlParameter paras = new SqlParameter("@id", id);
            if (dbHelper.ExecuteCommand(strSql, paras)>0)
            {
                return true;
            }
            else
            {
                return false;
            }            
        }

    }
}
